﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//Adding
using System.IO;
using PdfSharp.Pdf;
using PdfSharp.Pdf.AcroForms;
using PdfSharp.Pdf.IO;
using PdfSharp.Drawing;
using System.Data;
using KPIS.GERP.GFMS.APP.Reports;
using CrystalDecisions.Shared;
using KPIS.GERP.GFMS.MODEL;

namespace KPIS.GERP.GFMS.APP.App_UI.Forms
{
    public partial class RdsPopup : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        static string path;

        protected void Page_Load(object sender, EventArgs e)
        {
            DataTable dtbTax;
            dtbTax = GetTax1("2558");
            Tax1 rptTax1 = new Tax1();
            rptTax1.Load(@"..\..\Reports\Tax1.rpt");
            rptTax1.SetDataSource(dtbTax);
            //rptTax1.ExportToDisk(ExportFormatType.PortableDocFormat, "aaa.pdf");
            //rptTax1.Dispose();
            //rptTax1.Close();
            CrystalReportViewer1.ReportSource = rptTax1;
            CrystalReportViewer1.DataBind();
        }

        public DataTable GetTax1(string strYear)
        {
            var dbManager = new DBM.DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@year", strYear);

            string strSQL = @"
select e.citizen as CitizenId
, concat(np.prefix_name, e.first_name, ' ', e.last_name) as EmpName
, case when ad2.address_seq is not null
	then concat(coalesce(ad2.address_descr, ''), ' ', coalesce(d2.district_name, ''), ' ', coalesce(a2.amp_name, ''), ' ', coalesce(p2.prov_name, ''))
	else concat(coalesce(ad1.address_descr, ''), ' ', coalesce(d1.district_name, ''), ' ', coalesce(a1.amp_name, ''), ' ', coalesce(p1.prov_name, ''))
	end as Address
, case when DATEPART(year, e.date_work_start) = 2558 - 543 then e.date_work_start else null end as StartDate
--, case when e.mar_stat_seq in (2, 3) then 1 else 0 end as IsMarried
, 0 as IsMarried
, 0 as StudyChildCount
, 0 as NonStudyChildCount
, 0 as OtherDeduct
, prd.salary as SumSalary
, prd.tax as SumTax
from hrm_ps_employees e
inner join (select emp_seq, sum(coalesce(salary, 0)) as salary, sum(coalesce(tax, 0)) as tax
	from fim_payroll_dtls
	where [year] = @year
	group by emp_seq
	) prd on e.emp_seq = prd.emp_seq
left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq
--left join (select top 1 * from hrm_ps_emp_address where address_type_seq = 1 and record_status <> 'D' order by address_seq desc) ad1 on e.emp_seq = ad1.emp_seq
--left join (select top 1 * from hrm_ps_emp_address where address_type_seq = 2 and record_status <> 'D' order by address_seq desc) ad2 on e.emp_seq = ad2.emp_seq
left join hrm_ps_emp_address ad1 on e.emp_seq = ad1.emp_seq and ad1.record_status <> 'D' and ad1.address_type_seq = 1
left join hrm_ps_emp_address ad2 on e.emp_seq = ad2.emp_seq and ad2.record_status <> 'D' and ad2.address_type_seq = 2
left join mas_districts d1 on ad1.district_seq = d1.district_seq
left join mas_districts d2 on ad2.district_seq = d2.district_seq
left join mas_amphurs a1 on d1.amp_seq = a1.amp_seq
left join mas_amphurs a2 on d2.amp_seq = a2.amp_seq
left join mas_provinces p1 on a1.prov_seq = p1.prov_seq
left join mas_provinces p2 on a2.prov_seq = p2.prov_seq
--left join mas_marital_statuses ms on e.mar_stat_seq = ms.mar_stat_seq
";

            try
            {
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //private void GetPP30(string cmbMonthly, string cmbYear, string source) 
        //{
        //    string YearEn = (Convert.ToInt32(cmbYear) - 543).ToString();
        //    Dictionary<string, decimal> ret = ACMPAY.GetvalueAccPP(cmbMonthly, YearEn, source);
        //    if (ret.Count <= 0)
        //    {
        //        //Response.Write("   Not Found Data!");
        //        //return;
        //    }

        //    //******PdfSharp******//
        //    string filename = Path.Combine(path, "pp30.pdf");
        //    if (!File.Exists(filename))
        //        return;
        //    //Open document with convert compatible
        //    PdfDocument document = PdfReader.Open(filename, PdfDocumentOpenMode.Modify);//PdfReaderCompatible.Open(filename);

        //    PdfAcroForm form = document.AcroForm;
        //    // Get all form fields of the whole document
        //    PdfAcroField.PdfAcroFieldCollection fields = form.Fields;
        //    double fontSize = 16;
        //    string fontFam = "TH SarabunPSK";
        //    XFont myfont = new XFont(fontFam, fontSize, XFontStyle.Regular, new XPdfFontOptions(PdfFontEncoding.Unicode));

        //    PdfTextField txt = (PdfTextField)fields["txtYear"];
        //    txt.Font = myfont;
        //    PdfString strTmp = new PdfString(cmbYear);
        //    txt.Text = cmbYear;
        //    txt.Value = strTmp;
        //    PdfCheckBoxField c = (PdfCheckBoxField)fields["ckbMonth_" + cmbMonthly];
        //    c.Checked = true;

        //    decimal value = 0;
        //    PdfTextField txt1 = (PdfTextField)fields["txt1"];
        //    if (ret.ContainsKey("sumSaleAmount")) value = ret["sumSaleAmount"];
        //    strTmp = new PdfString(value.ToString("#,##0.00"));
        //    txt1.Font = myfont;
        //    txt1.Text = value.ToString("#,##0.00");
        //    txt1.Value = strTmp;

        //    value = 0;
        //    PdfTextField txt2 = (PdfTextField)fields["txt2"];
        //    strTmp = new PdfString(value.ToString("#,##0.00"));
        //    txt2.Font = myfont;
        //    txt2.Text = value.ToString("#,##0.00");
        //    txt2.Value = strTmp;

        //    value = 0;
        //    PdfTextField txt3 = (PdfTextField)fields["txt3"];
        //    strTmp = new PdfString(value.ToString("#,##0.00"));
        //    txt3.Font = myfont;
        //    txt3.Text = value.ToString("#,##0.00");
        //    txt3.Value = strTmp;
        //    decimal sumSaleAmount = 0;
        //    PdfTextField txt4 = (PdfTextField)fields["txt4"];
        //    if (ret.ContainsKey("sumSaleAmount")) sumSaleAmount = ret["sumSaleAmount"];
        //    strTmp = new PdfString(sumSaleAmount.ToString("#,##0.00"));
        //    txt4.Font = myfont;
        //    txt4.Text = sumSaleAmount.ToString("#,##0.00");
        //    txt4.Value = strTmp;

        //    decimal taxSale = 0;
        //    PdfTextField txt5 = (PdfTextField)fields["txt5"];
        //    if (ret.ContainsKey("taxSale")) taxSale = ret["taxSale"];
        //    strTmp = new PdfString(taxSale.ToString("#,##0.00"));
        //    txt5.Font = myfont;
        //    txt5.Text = taxSale.ToString("#,##0.00");
        //    txt5.Value = strTmp;

        //    value = 0;
        //    PdfTextField txt6 = (PdfTextField)fields["txt6"];
        //    if (ret.ContainsKey("sumBuyAmount")) value = ret["sumBuyAmount"];
        //    strTmp = new PdfString(value.ToString("#,##0.00"));
        //    txt6.Font = myfont;
        //    txt6.Text = value.ToString("#,##0.00");
        //    txt6.Value = strTmp;

        //    decimal taxBuy = 0;
        //    PdfTextField txt7 = (PdfTextField)fields["txt7"];
        //    if (ret.ContainsKey("taxBuy")) taxBuy = ret["taxBuy"];
        //    strTmp = new PdfString(taxBuy.ToString("#,##0.00"));
        //    txt7.Font = myfont;
        //    txt7.Text = value.ToString("#,##0.00");
        //    txt7.Value = strTmp;

        //    decimal val8 = 0; decimal val9 = 0;

        //    PdfTextField txt8 = (PdfTextField)fields["txt8"];
        //    txt8.Font = myfont;
        //    txt8.Text = val8.ToString("#,##0.00");
        //    if (taxSale > taxBuy)
        //    {
        //        val8 = taxSale - taxBuy;
        //        strTmp = new PdfString(val8.ToString("#,##0.00"));
        //        txt8.Text = val8.ToString("#,##0.00");
        //        txt8.Value = strTmp;
        //    }

        //    PdfTextField txt9 = (PdfTextField)fields["txt9"];
        //    txt9.Font = myfont;
        //    strTmp = new PdfString(val9.ToString("#,##0.00"));
        //    if (taxSale < taxBuy)
        //    {
        //        val9 = taxBuy - taxSale;
        //        strTmp = new PdfString(val9.ToString("#,##0.00"));
        //        txt9.Text = val9.ToString("#,##0.00");
        //        txt9.Value = strTmp;
        //    }

        //    decimal val10 = 0;
        //    PdfTextField txt10 = (PdfTextField)fields["txt10"];
        //    if (ret.ContainsKey("amountCr")) taxBuy = ret["amountCr"];
        //    strTmp = new PdfString(val10.ToString("#,##0.00"));
        //    txt10.Font = myfont;
        //    txt10.Text = val10.ToString("#,##0.00");
        //    txt10.Value = strTmp;

        //    decimal amountDr = 0;
        //    if (ret.ContainsKey("amountDr")) amountDr = ret["amountDr"];
        //    decimal val11 = 0; decimal val12 = 0;

        //    if (val8 > val10)
        //    {
        //        PdfCheckBoxField c11 = (PdfCheckBoxField)fields["ckb11"];
        //        c11.Checked = true;
        //        val11 = amountDr;
        //        PdfTextField txt11 = (PdfTextField)fields["txt11"];
        //        strTmp = new PdfString(val11.ToString("#,##0.00"));
        //        txt11.Font = myfont;
        //        txt11.Text = val11.ToString("#,##0.00");
        //        txt11.Value = strTmp;

        //        PdfTextField txt12 = (PdfTextField)fields["txt12"];
        //        strTmp = new PdfString((0).ToString("#,##0.00"));
        //        txt12.Font = myfont;
        //        txt12.Text = (0).ToString("#,##0.00");
        //        txt12.Value = strTmp;
        //    }

        //    if ((val8 < val10) || (val8 < val9 + val10))
        //    {
        //        PdfTextField txt11 = (PdfTextField)fields["txt11"];
        //        strTmp = new PdfString((0).ToString("#,##0.00"));
        //        txt11.Font = myfont;
        //        txt11.Text = (0).ToString("#,##0.00");
        //        txt11.Value = strTmp;

        //        PdfCheckBoxField c12 = (PdfCheckBoxField)fields["ckb12"];
        //        c12.Checked = true;

        //        val12 = val9 + val10;
        //        PdfTextField txt12 = (PdfTextField)fields["txt12"];
        //        strTmp = new PdfString(val10.ToString("#,##0.00"));
        //        txt12.Font = myfont;
        //        txt12.Text = val10.ToString("#,##0.00");
        //        txt12.Value = strTmp;
        //    }

        //    decimal val13 = 0;
        //    PdfTextField txt13 = (PdfTextField)fields["txt13"];
        //    txt13.Font = myfont;
        //    txt13.Text = val13.ToString("#,##0.00");

        //    decimal val14 = 0;
        //    PdfTextField txt14 = (PdfTextField)fields["txt14"];
        //    txt14.Font = myfont;
        //    txt14.Text = val14.ToString("#,##0.00");


        //    PdfTextField txt15 = (PdfTextField)fields["txt15"];
        //    strTmp = new PdfString(val11.ToString("#,##0.00"));
        //    txt15.Font = myfont;
        //    txt15.Text = val11.ToString("#,##0.00");
        //    txt15.Value = strTmp;

        //    PdfTextField txt16 = (PdfTextField)fields["txt16"];
        //    strTmp = new PdfString(val12.ToString("#,##0.00"));
        //    txt16.Font = myfont;
        //    txt16.Text = val12.ToString("#,##0.00");
        //    txt16.Value = strTmp;


        //    if (Session["UserName"] != null)
        //    {
        //        PdfTextField txts = (PdfTextField)fields["txtSignature"];
        //        strTmp = new PdfString(Session["UserName"].ToString());
        //        txts.Font = myfont;
        //        txts.Text = Session["UserName"].ToString();
        //        txts.Value = strTmp;
        //    }

        //    string dt = DateTime.Now.ToString("d MMMM yyyy", new System.Globalization.CultureInfo("th-TH"));
        //    PdfTextField txtd = (PdfTextField)fields["txtDate"];
        //    strTmp = new PdfString(dt);
        //    txtd.Font = myfont;
        //    txtd.Text = dt;
        //    txtd.Value = strTmp;

        //    //filename = filename.Substring(0, filename.Length - 4);
        //    //filename = filename + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".pdf";

        //    MemoryStream stream = new System.IO.MemoryStream();
        //    document.Save(stream, false);
        //    stream.Position = 0;
        //    Response.Clear();
        //    Response.ClearContent();
        //    Response.ClearHeaders();
        //    Response.ContentType = "application/pdf";
        //    Response.AddHeader("content-length", stream.Length.ToString());
        //    Response.BinaryWrite(stream.ToArray());
        //    Response.Flush();
        //    stream.Close();
        //    Response.End();
        //}
    }
}